package com.system.demo.mapper.dynamicSqlProvider;

import com.system.demo.entity.DTO.ApplicationSearchDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;


public class ApplicationDynamicSqlProvider {

    public String getCombinedApplicationData(@Param("search") ApplicationSearchDTO search) {
        return new SQL() {{
            SELECT_DISTINCT(
                    "sa.id",
                    "sa.uid",
                    "sa.name AS studentName",
                    "sa.student_class",
                    "sa.competition_name",
                    "sa.competition_tutor",
                    "sa.competition_time",
                    "sa.participation_mode",
                    "sa.competition_position",
                    "sa.participant_type",
                    "sa.team_members",
                    "sa.team_rank",
                    "sa.status",
                    "sa.create_time AS applicationCreateTime",
                    "cf.file_url",
                    "cf.reward_title",
                    "cf.create_time AS fileCreateTime",
                    "c.final_rank_name",
                    "c.final_rank_level",
                    "c.create_time AS cognizanceCreateTime"
            );
            FROM("submitApplication sa");
            LEFT_OUTER_JOIN("competition_file cf ON sa.id = cf.application_id");
            LEFT_OUTER_JOIN("cognizance c ON sa.id = c.application_id");

            if (search.getStatus() != null) {
                WHERE("sa.status = #{search.status}");
            }
            if (search.getStartTime() != null && !search.getStartTime().isEmpty()) {
                WHERE("sa.create_time >= #{search.startTime}");
            }
            if (search.getEndTime() != null && !search.getEndTime().isEmpty()) {
                WHERE("sa.create_time <= #{search.endTime}");
            }
            if (search.getUid() != null && !search.getUid().isEmpty()) {
                WHERE("sa.uid = #{search.uid}");
            }
            if (search.getCompetitionName() != null && !search.getCompetitionName().isEmpty()) {
                WHERE("sa.competition_name LIKE CONCAT('%', #{search.competitionName}, '%')");
            }
            if (search.getAppClass() != null && !search.getAppClass().isEmpty()){
                WHERE("sa.student_class LIKE CONCAT('%', #{search.appClass}, '%')");
            }
        }}.toString();
    }
}